package db;

import interfaces.IFSupplier;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import model.Customer;
import model.Supplier;

public class SupplierDb implements IFSupplier {

	protected Connection con;
	
	
	public SupplierDb() {
		connectDb();
	}
	
	public void connectDb() {
		con = DbConnection.getInstance().getDBcon();
	}

	public void disconnectDb() {
		DbConnection.getInstance();
		DbConnection.closeConnection();
	}


	@Override
	public ArrayList<Supplier> getAllSuppliers() throws Exception {
		String query = "SELECT * FROM Supplier JOIN Location ON Supplier.zipcode = Location.zipcode";
		ResultSet rs = null;
		ArrayList<Supplier> data = new ArrayList<>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Showing all suppliers ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				data.add(new Supplier(rs.getInt("supplierId"), rs.getString("name"), rs.getString("phoneNumber"), rs.getString("address"), rs.getString("email"), rs.getInt("zipcode"), rs.getString("country"),  rs.getString("city"), rs.getString("description"), rs.getInt("productId")));
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return data;
	}

	@Override
	public Supplier searchSupplier(String name, String phoneNumber,
			String address, String email, int zipcode, String country,
			String city, int productId) throws Exception {
		String sqlSearchName = "name LIKE '%" + name + "%' ";
		String sqlSearchPhoneNumber = !phoneNumber.equals("") ? "AND phoneNumber LIKE '%" + phoneNumber + "%' " : "";
		String sqlSearchAddress = !address.equals("") ? " AND address LIKE '%" + address + "%' " : "";
		String sqlSearchEmail = !email.equals("") ? " AND email LIKE '%" + email + "%' " : "";
		String sqlSearchCountry = !country.equals("") ? " AND country LIKE '%" + country + "%' " : "";
		String sqlSearchCity = !city.equals("") ? " AND city LIKE '%" + city + "%' " : "";
		String sqlSearchProductId = productId != -1 ? " AND productId LIKE '%" + productId + "%' " : "";
		sqlSearchProductId = "";
		String query = 
				"SELECT * FROM Supplier " +
				"JOIN Location ON Supplier.zipcode = Location.zipcode " +
				"WHERE " +
				sqlSearchName.toUpperCase() +
				sqlSearchPhoneNumber.toUpperCase() +
				sqlSearchAddress.toUpperCase() +
				sqlSearchEmail.toUpperCase() +
				sqlSearchCountry.toUpperCase() +
				sqlSearchCity.toUpperCase() +
				sqlSearchProductId.toUpperCase();
		ResultSet rs = null;
		ArrayList<Supplier> data = new ArrayList<>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Showing all suppliers ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				
				data.add(new Supplier(rs.getInt("supplierId"), rs.getString("name"), rs.getString("phoneNumber"), rs.getString("address"), rs.getString("email"), rs.getInt("zipcode"), rs.getString("country"),  rs.getString("city"), rs.getString("description"), productId));
				System.out.println(rs.getString("country"));
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		if (data.size() == 1)
		{ 
			return data.get(0);
		}
		else
		{
			return new Supplier();
		}
	}

	@Override
	public ArrayList<Supplier> searchSupplierList(String name,
			String phoneNumber, String address, String email, int zipcode,
			String country, String city, int productId) throws Exception {
		String sqlSearchName = "name LIKE '%" + name + "%' ";
		String sqlSearchPhoneNumber = !phoneNumber.equals("") ? "AND phoneNumber LIKE '%" + phoneNumber + "%' " : "";
		String sqlSearchAddress = !address.equals("") ? " AND address LIKE '%" + address + "%' " : "";
		String sqlSearchEmail = !email.equals("") ? " AND email LIKE '%" + email + "%' " : "";
		String sqlSearchCountry = !country.equals("") ? " AND country LIKE '%" + country + "%' " : "";
		String sqlSearchCity = !city.equals("") ? " AND city LIKE '%" + city + "%' " : "";
		String sqlSearchProductId = productId != -1 ? " AND productId LIKE '%" + productId + "%' " : "";
		
		String query = 
				"SELECT * FROM Supplier " +
				"JOIN Location ON Supplier.zipcode = Location.zipcode " +
				"WHERE " +
				sqlSearchName.toUpperCase() +
				sqlSearchPhoneNumber.toUpperCase() +
				sqlSearchAddress.toUpperCase() +
				sqlSearchEmail.toUpperCase() +
				sqlSearchCountry.toUpperCase() +
				sqlSearchCity.toUpperCase() +
				sqlSearchProductId.toUpperCase();
		ResultSet rs = null;
		ArrayList<Supplier> data = new ArrayList<>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Showing all suppliers ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				
				data.add(new Supplier(rs.getInt("supplierId"), rs.getString("name"), rs.getString("phoneNumber"), rs.getString("address"), rs.getString("email"), rs.getInt("zipcode"), rs.getString("country"),  rs.getString("city"), rs.getString("description"), productId));
				System.out.println(rs.getString("country"));
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return data;
	}

	@Override
	public void insertSupplier(String name, String phoneNumber, String address,
			String email, int zipcode, String description, int productId) throws Exception {
		String sqlName = name;
		String sqlPhoneNumber = phoneNumber;
		String sqlAddress = address;
		String sqlEmail = email;
		String sqlDescription = description;
		int sqlProductId = productId;
		int sqlZipcode = zipcode;
		String query = 
				"INSERT INTO Supplier (name, address, phoneNumber, email, description, zipcode, productId)" +
				"VALUES ('" +
				sqlName.toUpperCase() + "','" +
				sqlAddress.toUpperCase() + "','" +
				sqlPhoneNumber.toUpperCase() + "','" +
				sqlEmail.toUpperCase() + "','" +
				sqlDescription.toUpperCase() + "','" +
				sqlZipcode + "','" +
				sqlProductId + "')";
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Inserting Customer ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			//rs = stmt.executeQuery(query);
			
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
	}

	@Override
	public void removeSupplier(String name, String phoneNumber, String address, String country, String city, String email, int zipcode, int productId) throws Exception {
		String sqlName = name;
		String sqlPhoneNumber = phoneNumber;
		String sqlCountry = country;
		String sqlCity = city;
		String sqlAddress = address;
		String sqlEmail = email;
		int sqlProductId = productId;
		int sqlZipcode = zipcode;
		int sqlId = searchSupplier(sqlName, sqlPhoneNumber, sqlAddress, sqlEmail, sqlZipcode, sqlCountry, sqlCity, sqlProductId).getSupplierId();
		System.out.println("ID  = " + sqlId);
		String query = 
				"DELETE FROM Supplier " +
				"WHERE supplierId='" + sqlId + "'";
		if(sqlId > 0)
		{
			System.out.println("QUERRY : " + query);
			try {
				System.out.println("Deleting Supplier ...");
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				stmt.executeUpdate(query);
				//rs = stmt.executeQuery(query);
				
				stmt.close();
				System.out.println("SUCCESS: Querry executed!");
			}// end try
			catch (SQLException ex) {
				System.out.println("ERROR:" + ex.getMessage());
			}
			JOptionPane.showMessageDialog(null, "Supplier deleted!");
		}
		else
		{
			JOptionPane.showMessageDialog(null, "The supplier does not exist or there is more than one Supplier with specified information.");
		}
		
	}

}
